<?
session_start();
include('conn.php');

$custid = $_SESSION['custid'];
$custtype = $_SESSION['custtype'];
//$year = $_REQUEST['year'];


 $qry1 = "select custtype,customer , year, 
sum(Jan) Jan, 
sum(Feb) Feb,
sum(mar) Mar,
sum(apr) Apr,
sum(may) May,
sum(jun) Jun,
sum(jul) Jul,
sum(aug) Aug,
sum(sep) Sep,
sum(oct) Oct,
sum(nov) Nov,
sum(Dece) Dece,
sum(Jan) +sum(Feb) +sum(mar) +sum(apr) + sum(may) + sum(jun) +sum(jul) +sum(aug) +sum(sep) +sum(oct) + sum(nov) +sum(Dece) grandtotal

 from
(
select custtype, customer, year, 
case when month = 1 then total else 0 end 'Jan',
case when month = 2 then total else 0 end 'Feb',
case when month = 3 then total else 0 end 'Mar',
case when month = 4 then total else 0 end 'Apr',
case when month = 5 then total else 0 end 'May',
case when month = 6 then total else 0 end 'Jun',
case when month = 7 then total else 0 end 'Jul',
case when month = 8 then total else 0 end 'Aug',
case when month = 9 then total else 0 end 'Sep',
case when month = 10 then total else 0 end 'Oct',
case when month = 11 then total else 0 end 'Nov',
case when month = 12 then total else 0 end 'Dece'

from
(
SELECT c.custtype, c.custdesc customer, Year( t.trndate ) AS year, MONTH( t.trndate ) month, sum( t.debitamt ) total
FROM `customermast` c
LEFT JOIN `transactionmast` t ON c.custid = t.custid
where t.status = 0
GROUP BY c.custtype, c.custdesc, Year( t.trndate ) , MONTH( t.trndate )
) as r

) as JJ
where year is not null

group by custtype,customer, year
order by customer, custtype,year";


 // SELECT c.custtype, c.custdesc customer, Year( t.trndate ) AS year, MONTHname( t.trndate ) , sum( t.debitamt )
// FROM `customermast` c
// LEFT JOIN `transactionmast` t ON c.custid = t.custid
// WHERE c.custid =171
// GROUP BY c.custtype, c.custdesc, Year( t.trndate ) , MONTHname( t.trndate )
// LIMIT 0 , 30 


 /* 
 */
// if ($custid >0)
// {
    // $qry1=$qry1." and c.custid=".$custid;
// }

// if ($custtype !="0")
// {
    // $qry1=$qry1." and c.custtype='".$custtype."'";
// }

// $qry1 =$qry1." group by c.custid,c.custdesc,c.custtype,t.status having IF( (sum(t.debitamt) - sum(t.creditamt)) is NULL,0,(sum(t.debitamt) - sum(t.creditamt)) )  != 0 and t.status = 0 order by sum(t.debitamt) - sum(t.creditamt) desc";

//echo $qry1;
$qry = mysql_query($qry1);
$data = array();
$i=1;
while($rows = mysql_fetch_array($qry))
{
				
					$data[] = array("Sr_NO" => $i++,
					"custtype" => $rows['custtype'],
					 "customer" => $rows['customer'],
                     "year" => $rows['year'],	
					 "Jan" => $rows['Jan']*1,
					 "Feb" => $rows['Feb']*1,
					 "Mar" => $rows['Mar']*1,
					 "Apr" => $rows['Apr']*1,
					 "May" => $rows['May']*1,
					 "Jun" => $rows['Jun']*1,
					 "Jul" => $rows['Jul']*1,
					 "Aug" => $rows['Aug']*1,
					 "Sep" => $rows['Sep']*1,
					 "Oct" => $rows['Oct']*1,
					 "Nov" => $rows['Nov']*1,
					 "Dec" => $rows['Dece']*1,
					 "grandtotal" => $rows['grandtotal']*1
					 //"outstdamt" => 0
                     //"report" =>	sprintf("outprint_report.php?custid=%d&custdesc=%s", $rows['custid'],$rows['custdesc']) 
                    );
}
echo json_encode($data);
?>
